from flask_paginate import Pagination, get_page_parameter, get_page_args
import pymysql
from flask import Flask, render_template, request
import numpy as np
def open_db():
    """
    建立数据库连接
    :return: 数据数据
    """
    db = pymysql.connect(host="localhost", user="root", password="1234", database="dv_project", charset="utf8")
    return db
def select_byname(DM_name):
    """
    不带参数查询
    :param sql:
    :return:
    """
    db = open_db()
    cursor = db.cursor()  # 使用cursor（）方法获取游标
    sql = f"SELECT dbid FROM vertical_model where name= %s;"
    cursor.execute(sql,(DM_name,))  # 执行sql查询语句
    result = cursor.fetchall()  # 记录查询结果
    cursor.close()  # 关闭游标
    db.close()  # 关闭数据库连接
    return result  # 返回查询结果

def select_bypage(offset=0, per_page=10):
    # 打开数据库连接
    db = open_db()

    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()

    # SQL 查询语句
    sql = f"SELECT name FROM Vertical_model LIMIT {offset}, {per_page} ;"

    # 执行 SQL 查询
    cursor.execute(sql)

    # 获取查询结果
    results = cursor.fetchall()
    array = [list(item) for item in results]
    res = [item for sublist in array for item in sublist]
    # 关闭数据库连接
    db.close()

    return res
# app = Flask(__name__)

# 配置分页
# app.config['PER_PAGE'] = 10

# Flask 模板中的分页函数
def paginated_view(data, template):
    page, per_page, offset = get_page_args(page_parameter='page', per_page_parameter='per_page')
    total = len(data)
    pagination = Pagination(page=page, per_page=per_page, total=total, css_framework='bootstrap4')
    return render_template(template, data=data[offset: offset + per_page], pagination=pagination, )

# Flask 路由和视图函数
def index():
    # 获取页面参数：页码和每页结果数
    page = request.args.get(get_page_parameter(), type=int, default=1)

    # 计算偏移量
    offset = (page - 1) * 10

    # 获取结果
    results = select_bypage(offset, 10)

    print(results)
    # 在模板页面中渲染分页结果
    # return paginated_view(data=results, template='index.html')

if __name__ == "__main__":
    # sql = "select * from vertical_model"
    # tuple = query1(sql)
    # print(tuple)
    data=select_bypage(0,8)
    print(data)

